package common;

public class SQLTemplate {
	public final static String CUSTOMER_SELECT_JOIN_CUSTOMER_TYPE_OPERATOR_SQL = "SELECT T_CUSTOMER.ID,"
			+ "T_CUSTOMER.NAME,"
			+ "T_CUSTOMER.SEX,"
			+ "T_CUSTOMER.AGE,"
			+ "T_CUSTOMER.IDENTITY_CARD,"
			+ "T_CUSTOMER.MOBILE_PHONE,"
			+ "T_CUSTOMER.TELEPHONE,"
			+ "T_CUSTOMER.ADDRESS,"
			+ "T_CUSTOMER_TYPE.ID AS CUSTOMER_TYPE_ID,"
			+ "T_CUSTOMER_TYPE.TYPE AS CUSTOMER_TYPE,"
			+ "T_OPERATOR.ID AS OPERATOR_ID,"
			+ "T_OPERATOR.NAME AS OPERATOR_NAME "
			+ "FROM T_CUSTOMER,T_CUSTOMER_TYPE,T_OPERATOR "
			+ "WHERE T_CUSTOMER.CUSTOMER_TYPE_ID=T_CUSTOMER_TYPE.ID "
			+ "AND T_CUSTOMER.OPERATOR_ID=T_OPERATOR.ID ";
	public final static String OPERATOR_SELECT_JOIN_ADMIN_RIGHT_SQL = "SELECT T_OPERATOR.ID,"
			+ "T_OPERATOR.NAME,"
			+ "T_OPERATOR.SEX,"
			+ "T_OPERATOR.AGE,"
			+ "T_OPERATOR.IDENTITY_CARD,"
			+ "T_OPERATOR.PASSWORD,"
			+ "T_OPERATOR.MOBILE_PHONE,"
			+ "T_OPERATOR.ADDRESS,"
			+ "T_OPERATOR.BANK_CARD,"
			+ "T_OPERATOR.SALARY,"
			+ "T_RIGHT.RIGHTS,"
			+ "ADMIN.ID AS ADMIN_ID,"
			+ "ADMIN.NAME AS ADMIN_NAME "
			+ "FROM T_OPERATOR,T_OPERATOR ADMIN,T_RIGHT "
			+ "WHERE T_OPERATOR.ADMIN_ID=ADMIN.ID "
			+ "AND T_OPERATOR.ID=T_RIGHT.OPERATOR_ID ";
	/**
	 * T_CUSTOMER SELECT SQL 客户表选择SQL
	 */
	public final static String POLICY_SELECT_JOIN_POLICY_TYPE_SQL = "SELECT T_POLICY.ID,"
			+ "T_POLICY.TITLE,"
			+ "T_POLICY.TIME,"
			+ "T_POLICY_TYPE.ID AS POLICY_TYPE_ID,"
			+ "T_POLICY_TYPE.TYPE AS POLICY_TYPE "
			+ "FROM T_POLICY,T_POLICY_TYPE "
			+ "WHERE T_POLICY.POLICY_TYPE_ID=T_POLICY_TYPE.ID ";
	public static final String FLLOW_CASE_SELECT_JOIN_CUSTOMER_OPERATOR_SQL = "SELECT T_FLLOW_CASE.ID,"
			+ "T_FLLOW_CASE.RECORD,"
			+ "T_FLLOW_CASE.NOTE,"
			+ "T_FLLOW_CASE.TIME,"
			+ "T_CUSTOMER.ID AS CUSTOMER_ID,"
			+ "T_CUSTOMER.NAME AS CUSTOMER_NAME,"
			+ "T_OPERATOR.ID AS OPERATOR_ID,"
			+ "T_OPERATOR.NAME AS OPERATOR_NAME "
			+ "FROM T_FLLOW_CASE,T_CUSTOMER,T_OPERATOR "
			+ "WHERE T_FLLOW_CASE.CUSTOMER_ID=T_CUSTOMER.ID "
			+ "AND T_FLLOW_CASE.OPERATOR_ID=T_OPERATOR.ID ";
	public static final String TRADE_LOG_SELECT_JOIN_CUSTOMER_OPERATOR_SQL = "SELECT T_TRADE_LOG.ID,"
			+ "T_TRADE_LOG.RECORD,"
			+ "T_TRADE_LOG.NOTE,"
			+ "T_TRADE_LOG.TIME,"
			+ "T_CUSTOMER.ID AS CUSTOMER_ID,"
			+ "T_CUSTOMER.NAME AS CUSTOMER_NAME,"
			+ "T_OPERATOR.ID AS OPERATOR_ID,"
			+ "T_OPERATOR.NAME AS OPERATOR_NAME "
			+ "FROM T_TRADE_LOG,T_CUSTOMER,T_OPERATOR "
			+ "WHERE T_TRADE_LOG.CUSTOMER_ID=T_CUSTOMER.ID "
			+ "AND T_TRADE_LOG.OPERATOR_ID=T_OPERATOR.ID ";
	public static final String RIGHT_SELECT_JOIN_OPERATOR_SQL = "SELECT T_RIGHT.ID,"
			+ "T_RIGHT.RIGHTS,"
			+ "T_OPERATOR.ID AS OPERATOR_ID,"
			+ "T_OPERATOR.NAME AS OPERATOR_NAME "
			+ "FROM T_RIGHT,T_OPERATOR "
			+ "WHERE T_RIGHT.OPERATOR_ID=T_OPERATOR.ID ";

	public static final String MESSAGE_SELECT_TEMPLATE = "SELECT * FROM T_MESSAGE ORDER BY TIME DESC ";
	public static final String CUSTOMER_TYPE_SELECT_TEMPLATE = "SELECT * FROM T_CUSTOMER_TYPE ";
	public static final String POLICY_TYPE_SELECT_TEMPLATE = "SELECT * FROM T_POLICY_TYPE ";
	public static final String POLICY_ARTICLE_SELECT_TEMPLATE = "SELECT ARTICLE FROM T_POLICY ";

	/**
	 * @param tableName
	 *            表名
	 * @param columnNames
	 *            列名
	 * @param values
	 *            列值
	 * @return addSQLTemplate 向该表添加数据SQL
	 */
	public static String addSQLTemplate(String tableName, String[] columnNames,
			String[] values) {
		int blastItemIndex = columnNames.length - 1;
		String SQL = "INSERT INTO " + tableName + "(";
		for (int i = 0; i < blastItemIndex; i++) {
			SQL += columnNames[i] + ",";
		}
		SQL += columnNames[blastItemIndex] + ") VALUES (";
		for (int i = 0; i < blastItemIndex; i++) {
			if (values[i] == null || values[i].trim().isEmpty()) {
				values[i] = "0";
			}
			SQL += "'" + values[i] + "',";
		}
		if (values[blastItemIndex] == null
				|| values[blastItemIndex].trim().isEmpty()) {
			values[blastItemIndex] = "0";
		}
		SQL += "'" + values[blastItemIndex] + "');";
		return SQL;
	}

	/**
	 * @param tableName
	 *            表名
	 * @param columnNames
	 *            列名
	 * @param values
	 *            列值
	 * @return updateSQLTemplate 更新该表数据SQL
	 */
	public static String updateSQLTemplate(String tableName,
			String[] columnNames, String[] values) {
		int blastItemIndex = columnNames.length - 1;
		String SQL = "UPDATE " + tableName + " SET ";
		for (int i = 0; i < blastItemIndex; i++) {
			if (values[i] == null || values[i].trim().isEmpty()) {
				values[i] = "0";
			}
			SQL += columnNames[i] + "='" + values[i] + "',";
		}
		if (values[blastItemIndex] == null
				|| values[blastItemIndex].trim().isEmpty()) {
			values[blastItemIndex] = "0";
		}
		SQL += columnNames[blastItemIndex] + "='" + values[blastItemIndex]
				+ "' ";
		return SQL;
	}

	/**
	 * @param tableName
	 *            表名
	 * @return deleteSQLTemplate 删除该表数据SQL
	 */
	public static String deleteSQLTemplate(String tableName) {
		return "DELETE FROM " + tableName + " ";
	}

	/**
	 * @param tableName
	 *            表名
	 * @return countSizeSQLTemplate 计算该表有多少条数据SQL
	 */
	public static String countSizeSQLTemplate(String tableName) {
		return "SELECT COUNT(*) FROM " + tableName;
	}

	public static void main(String[] args) {
		// System.out.println(OPERATOR_SELECT_JOIN_TABLE_SQL);
		String tableName = "T_CUSTOMER";
		String[] columnNames = { "Name", "age" };
		String[] values = { "张三丰", "11" };
		System.out.println(addSQLTemplate(tableName, columnNames, values));
		System.out.println(updateSQLTemplate(tableName, columnNames, values)
				+ "WHERE NAME='刘学炜';");
	}

}
